EMT Practice Test

1. Question Content...

---

Question1: You are ingesting data from an AWS S3 bucket into a Snowflake table using a COPY INTO statement. The COPY INTO command fails with an error indicating 'Invalid stage location specified'. You have verified that the stage name is correct and the Snowflake user has the necessary privileges to access the stage. However, the error persists. Which of the following are potential causes and solutions for this issue?

Question2: You are tasked with creating a resilient data pipeline using Snowpark Python. The pipeline transforms data from a raw stage to a processed stage. A key transformation involves joining two DataFrames, 'dfl' and 'df2 , based on a common column, 'id'. You want to ensure that even if 'df2 is temporarily unavailable or contains unexpected data, the pipeline continues to process 'dfl' using a default value for missing data from 'df2. Which of the following approaches provides the best balance of resilience and data integrity? Assume you have defined a default dataframe 'df default' already.

Question3: You are tasked with creating a Snowpark Python UDF that calculates the exponential moving average (EMA) of a time series dataset stored in a Snowflake table named 'SALES DATA'. The table has columns 'TIMESTAMP' (TIMESTAMP_NTZ) and 'SALES' (NUMBER). The EMA should be calculated for each product, identified by the 'PRODUCT ID' column. You want to optimize the calculation by using a Pandas DataFrame within the UDF and leveraging vectorized operations. Which of the following code snippets would be the MOST efficient and correct way to achieve this? Assume 'alpha' is a predefined float variable representing the smoothing factor.

Question4: You are responsible for monitoring a critical data pipeline that loads data from an external Kafka topic into a Snowflake table 'ORDERS' Data anomalies have been frequently observed, impacting downstream reporting. You want to implement a solution that proactivelyidentifies and alerts on data quality issues such as missing values, invalid formats, and unexpected data distributions. Which combination of Snowflake features and approaches would be MOST effective for achieving this objective with minimal performance overhead on the pipeline itself?

Question5: You have implemented a masking policy on the 'SSN' column of the 'EMPLOYEES' table. You now need to suspend the masking policy temporarily for a specific batch job that requires access to the unmasked data'. What is the recommended way to achieve this without dropping the masking policy or altering the user's role?

Question6: You have implemented a Snowpipe using auto-ingest to load data from an AWS S3 bucket. The pipe is configured to load data into a table with a 'DATE column ('TRANSACTION DATE'). The data files in S3 contain a date field in the format 'YYYYMMDD'. Occasionally, you observe data loading failures in Snowpipe with the error message indicating an issue converting the string to a date. The 'FILE FORMAT' definition includes 'DATE FORMAT = 'YYYYMMDD''. Furthermore, you are also noticing that after a while, some files are not being ingested even though they are present in the S3 bucket. How to effectively diagnose and resolve these issues?

Question7: You have configured a Kafka Connector to load JSON data into a Snowflake table named 'ORDERS. The JSON data contains nested structures. However, Snowflake is only receiving the top- level fields, and the nested fields are being ignored. Which configuration option within the Kafka Connector needs to be adjusted to correctly flatten and load the nested JSON data into Snowflake?

Question8: You are working with a Snowpark DataFrame named 'customer data' that contains sensitive Personally Identifiable Information (PII). The DataFrame has columns such as 'customer id', 'name', 'email' , and 'phone number'. Your task is to create a new DataFrame that only contains 'customer id' and a hash of the 'email' address for anonymization purposes, while also filtering out any customers whose 'customer id' starts with 'TEMP'. Which of the following approaches adheres to best practices for data security and efficiency in Snowpark, using secure hashing algorithms provided by Snowflake?

Question9: Consider a table with columns and 'customer _ region'. You want to implement both a Row Access Policy (RAP) and an Aggregation Policy on this table. The RAP should restrict access to orders based on the user's region, defined in a session variable 'CURRENT REGION'. Users should only see orders from their region. The Aggregation Policy should mask order totals for regions other than the user's region when aggregating data'. In other words if someone attempts to aggregate ALL region's totals, the aggregation will only include their region. Which statements about implementing this scenario are true?

Question10: You are building a data pipeline to ingest clickstream data into Snowflake. The raw data is landed in a stage and you are using a Stream on this stage to track new files. The data is then transformed and loaded into a target table 'CLICKSTREAM DATA. However, you notice that sometimes the same files are being processed multiple times, leading to duplicate records in 'CLICKSTREAM DATA. You are using the 'SYSTEM$STREAM HAS DATA' function to check if the stream has data before processing. What are the possible reasons this might be happening, and how can you prevent it? (Select all that apply)

Question11: A large e-commerce company stores clickstream data in an AWS S3 bucket. The data is partitioned by date and consists of Parquet files. They need to analyze this data in Snowflake without physically moving it into Snowflake's internal storage. However, the data frequently changes, and they need to ensure queries reflect the latest updates to the files without significant latency. Which of the following approaches would be MOST suitable, considering cost, performance, and data freshness?

Question12: You are tasked with optimizing a continuous data pipeline that loads data from an external stage into a Snowflake table using streams.
The pipeline is experiencing significant latency during peak hours. The stream is defined on a very large table with frequent updates and deletes. Which of the following strategies would be MOST effective in reducing the latency of the data pipeline, considering stream performance and cost implications?

Question13: You are using the Snowflake Spark connector to update records in a Snowflake table based on data from a Spark DataFrame. The Snowflake table 'CUSTOMER' has columns 'CUSTOMER ID' (primary key), 'NAME, and 'ADDRESS'. You have a Spark DataFrame with updated 'NAME and 'ADDRESS' values for some customers. To optimize performance and minimize data transfer, which of the following strategies can you combine with a temporary staging table to perform an efficient update?

Question14: A data provider wants to share a large dataset (several TB) with multiple consumers. The dataset is updated daily. The provider wants to minimize the cost associated with data sharing and ensure that consumers receive consistent data'. Which of the following strategies would be the MOST cost-effective and maintainable?

Question15: You have created a secure external function that uses a Snowflake secret to retrieve a cryptographic key and mask sensitive data'. However, users are still able to see the sensitive data'. Which of the following actions is MOST likely to resolve the issue?

Question16: You've created a JavaScript UDF in Snowflake to perform complex string manipulation. You need to ensure this UDF can handle a large volume of data efficiently. The UDF is defined as follows:

When testing with a large dataset, you observe poor performance. Which of the following strategies, when applied independently or in combination, would MOST likely improve the performance of this UDF?

Question17: A data engineering team has implemented a continuous data pipeline that loads data into a Snowflake table named 'SALES DATA' They notice that the pipeline intermittently experiences performance degradation, particularly during peak business hours. The team wants to implement alerts to proactively identify and address these performance issues. Which of the following approaches would be MOST effective for monitoring the pipeline and triggering alerts based on specific performance metrics related to data loading?

Question18: Consider a scenario where you have a Snowflake external table 'ext_logs' pointing to log files in an S3 bucket. The log files are continuously being updated, and new files are added frequently. You want to ensure that your external table always reflects the latest data available in S3. Which of the following actions and configurations are required or recommended to keep the external table synchronized with the underlying data source? (Select all that apply)

Question19: A data engineering team is tasked with optimizing a complex query that joins three tables: 'ORDERS' , 'CUSTOMERS' , and 'PRODUCTS. The 'ORDERS' table contains millions of records and is frequently joined with 'CUSTOMERS' (containing customer demographics) and 'PRODUCTS' (containing product details). The initial query uses standard JOIN syntax, but performance is slow. The query retrieves order details along with customer and product information, filtering by a specific date range in the 'ORDERS' table and a customer segment in the 'CUSTOMERS table. Which optimization strategy would be MOST effective for significantly improving query performance?

Question20: You need to load data from a stream of CSV files into a Snowflake table. The CSV files are delivered to an AWS S3 bucket and contain header rows. The files occasionally include records where a text field contains a delimiter character (comma) within the text itself, but these fields are properly enclosed within double quotes. You want to create a file format object that correctly handles the data, including quoted delimiters, and skips the header row. Which of the following file format options are required to achieve this? (Choose two)

Question21: A data engineer is investigating high credit consumption on a Snowflake warehouse due to frequent re-clustering operations on a large table named 'WEB EVENTS. This table is clustered on 'EVENT TIMESTAMP' and 'USER ID. The engineer suspects that the high frequency of data ingestion, especially out-of-order 'EVENT TIMESTAMP' values, contributes to the poor clustering. Choose the options that can lead to optimizing clustering and reducing credit consumption, assuming you have limited control over the ingestion process and data quality.

Question22: You are using Snowpark Python to perform data transformation on a large dataset stored in a Snowflake table named customer transactions'. This table contains columns such as 'customer id', 'transaction date', 'transaction amount', and product_category'. Your task is to identify customers who have made transactions in more than one product category within the last 30 days. Which of the following Snowpark Python snippets is the most efficient way to achieve this, minimizing data shuffling and maximizing query performance?

Question23: A data engineer is using the Snowflake Spark connector to write data to a Snowflake table. The write operation fails consistently with the error 'net.snowflake.client.jdbc.SnowflakeSQLException: SQL execution error: String '. ' is too long (maximum is 16777216)'. Which of the following is the most likely cause and how can it be resolved using Spark Connector?

Question24: You accidentally truncated a large table named 'SALES DATA' in your 'REPORTING DB" database. You realize this happened 2 days ago, and your account has the default Time Travel retention of 1 day. You need to recover this table with minimal downtime. Analyze the situation and determine the best course of action, considering cost and recovery time.

Question25: You are designing a data protection strategy for a Snowflake environment that processes sensitive payment card industry (PCI) data'. You decide to use a combination of column-level security and external tokenization. Which of the following statements are TRUE regarding the advantages of using both techniques together? (Select TWO)

Question26: You are designing a data protection strategy for a Snowflake database. You need to implement dynamic data masking on the 'CREDIT CARD' column in the 'TRANSACTIONS' table. The requirement is that users with the 'FINANCE ADMIN' role should see the full credit card number, while all other users should see only the last four digits. You have the following masking policy:

What is the next step to apply this masking policy to the 'CREDIT CARD' column?

Question27: A financial services company is implementing Snowflake. They have a table 'CUSTOMER DATA' containing sensitive information like 'CREDIT CARD NUMBER, 'SSN', and 'ADDRESS'. They need to ensure that: 1) Analysts can only see the last four digits of the 'CREDIT CARD NUMBER. 2) Data scientists require full access to the 'ADDRESS' but should not see the 'SSN'. 3) A dedicated compliance role should be able to view all data in its original format for auditing purposes. Which of the following is the MOST efficient and secure approach to implement this using Snowflake's data masking and RBAC?

Question28: You are tasked with building a Snowpipe to ingest JSON data from an AWS S3 bucket into a Snowflake table named 'SALES DATA'. The data is landing in the bucket frequently, and you want to use Snowpipe's auto-ingest feature. However, you are observing significant latency in data appearing in your Snowflake table after it lands in S3, despite verifying that S3 event notifications are correctly configured and the Snowflake event queue is receiving them. You've already checked that the pipe is enabled and has the necessary permissions. The Snowflake Pipe definition is as follows:

What is the MOST LIKELY reason for this delay, and what steps can you take to further troubleshoot?

Question29: You are tasked with implementing a projection policy in Snowflake to restrict access to certain columns of the 'EMPLOYEE table based on the user's role. The table contains columns like 'EMPLOYEE 'NAME, 'SALARY', and 'DEPARTMENT. Users with the 'HR MANAGER role should have access to all columns, while other users should only be able to see 'EMPLOYEE ID, 'NAME, and DEPARTMENT. The initial attempt to create the projection policy results in an error. What could be the reasons?

Question30: You are developing a JavaScript UDF in Snowflake to perform complex data validation on incoming data'. The UDF needs to validate multiple fields against different criteria, including checking for null values, data type validation, and range checks. Furthermore, you need to return a JSON object containing the validation results for each field, indicating whether each field is valid or not and providing an error message if invalid. Which approach is the MOST efficient and maintainable way to structure your JavaScript UDF to achieve this?

Question31: You are configuring cross-cloud replication for a Snowflake database named 'SALES DB' from an AWS (us-east-I) account to an Azure (eastus) account. You have already set up the necessary network policies and security integrations. However, replication is failing with the following error: 'Replication of database SALES DB failed due to insufficient privileges on object 'SALES DB.PUBLIC.ORDERS'.' What is the MOST LIKELY cause of this issue, and how would you resolve it? (Assume the replication group and target database exist).

Question32: You are tasked with processing streaming data in Snowflake using Snowpark Python. The raw data arrives in a DataFrame raw events' with the following schema: 'event id: string', 'event_time: timestamp', 'user id: string', and 'event data: string'. You need to perform the following data transformations: 1 . Extract a specific value from the JSON 'event_data' using the 'get' function to find the 'product_id' and create a new column named 'product id' of type STRING. 2. Filter the DataFrame to include only events where the is NOT NULL and the is within the last hour. 3. Aggregate the filtered data to count the number of events per 'product id'. Which of the following code snippets correctly performs these transformations in an efficient and performant manner?

Question33: You're building a data product on the Snowflake Marketplace that includes a view that aggregates data from a table containing Personally Identifiable Information (PII). You need to ensure that consumers of your data product CANNOT directly access the underlying PII data but can only see the aggregated results from the view. What is the MOST secure and recommended approach to achieve this?

Question34: A Snowflake data pipeline ingests data from multiple external sources into a RAW DATA table. A transformation process then moves the data to a ANALYTICS DATA table, applying several complex UDFs written in Java and Python for data cleansing and enrichment. Performance is significantly slower than expected. Which combination of techniques would BEST improve the performance of this transformation pipeline?

Question35: You are designing a data pipeline in Snowflake that involves several tasks chained together. One of the tasks, 'task B' , depends on the successful completion of 'task A'. 'task_B' occasionally fails due to transient network issues. To ensure the pipeline's robustness, you need to implement a retry mechanism for 'task_B' without using external orchestration tools. What is the MOST efficient way to achieve this using native Snowflake features, while also limiting the number of retries to prevent infinite loops and excessive resource consumption? Assume the task definition for 'task_B' is as follows:

Question36: You are developing a JavaScript stored procedure in Snowflake using Snowpark to perform a complex data transformation. This transformation involves multiple steps: filtering, joining with another table, and aggregating data'. You need to ensure that the stored procedure is resilient to failures and can be easily debugged. Which of the following practices would contribute to the robustness and debuggability of your stored procedure? (Select all that apply)

Question37: A data engineer is using Snowpark Scala to create a UDF that calculates the distance between two geographical coordinates (latitude and longitude) using the Haversine formula'. The function should accept four 'Double' values (latl, lonl , lat2, lon2) and return the distance in kilometers as a 'Double'. The UDF must be named 'haversine distance'. What is the correct Scala code to define and register this UDF with Snowflake, including the import statements required for using Snowpark functions?

Question38: A healthcare provider stores patient data in Snowflake, including 'PATIENT ID', 'NAME, 'MEDICAL HISTORY , and 'INSURANCE ID. They need to comply with HIPAA regulations. As a data engineer, you need to ensure that PHI (Protected Health Information) is masked appropriately based on user roles. Which of the following steps are NECESSARY to achieve this using Snowflake's data masking features and RBAC? (Select all that apply)

Question39: You have a table 'ORDERS in your Snowflake database. You are implementing a new data transformation pipeline. Before deploying the pipeline to production, you want to validate the changes in a development environment. You decide to use Time Travel to create a snapshot of the 'ORDERS' table before the transformation and compare it with the transformed data'. Which sequence of SQL commands would best facilitate this validation, assuming your development database and schema structure mirrors production?

Question40: A data engineer wants to use Snowpark to read a large CSV file from an external stage and infer the schema automatically. However, some columns in the CSV contain data that Snowflake cannot automatically infer the type for. Which of the following code snippets demonstrates the CORRECT way to read the CSV file with schema inference and handle potentially problematic columns by explicitly specifying their data types?

Question41: You are using Snowpark to perform a complex join operation between two large tables: 'ORDERS (1 OOGB) and 'CUSTOMER (50GB). The join is performed on 'ORDERS.CUSTOMER ID = CUSTOMER.ID. The query is running slower than expected. You have already confirmed that the warehouse size is adequate. Which of the following strategies, applied in combination , would most likely improve the join performance within a Snowpark context?

Question42: You have a Snowflake stage pointing to an external cloud storage location containing numerous Parquet files. A directory table is created on top of it. Over time, some files are deleted or moved from the external location. You notice discrepancies between the directory table's metadata and the actual files present in the storage location. Choose the option that best describes how Snowflake handles these discrepancies and the actions you should take.

Question43: You are responsible for monitoring data quality in a Snowflake data warehouse. Your team has identified a critical table, 'CUSTOMER DATA, where the 'EMAIL' column is frequently missing or contains invalid entries. You need to implement a solution that automatically detects and flags these anomalies. Which of the following approaches, or combination of approaches, would be MOST effective in proactively monitoring the data quality of the 'EMAIL' column?

Question44: You are using Snowpipe to ingest data from Azure Blob Storage into a Snowflake table. You have successfully set up the pipe and configured the event notifications. However, you notice that duplicate records are appearing in your target table. After reviewing the logs, you determine that the same file is being processed multiple times by Snowpipe. Which of the following strategies can you implement to prevent duplicate data ingestion, assuming you cannot modify the source data in Azure Blob Storage to include a unique ID or timestamp?

Question45: A data engineering team observes that queries against a large fact table ('SALES FACT') are slow, even after clustering and partitioning. The table contains columns like 'SALE ID', 'PRODUCT ID, 'CUSTOMER D', 'SALE DATE', 'QUANTITY', and 'PRICE' Queries commonly filter on 'PRODUCT ID' and 'SALE DATE. After implementing search optimization on these two columns, performance only marginally improves. You suspect the data distribution for 'PRODUCT ID' might be skewed. What steps can you take to further investigate and improve query performance?

Question46: You're working on a data transformation pipeline in Snowflake. You need to create a SQL UDF called that accepts the following parameters: 'price' (NUMBER) (NUMBER) (BOOLEAN) The function should calculate the final price after applying the discount. If the customer , an additional 5% discount should be applied on top of the initial discount. Choose all the valid SQL UDF definitions that accurately implement the requirements. Only one Discount Percentage needs to apply to the base price.

Question47: You're designing a Snowpark data transformation pipeline that requires running a Python function on each row of a large DataFrame. The Python function is computationally intensive and needs access to external libraries. Which of the following approaches will provide the BEST combination of performance, scalability, and resource utilization within the Snowpark architecture?

Question48: You have a complex data pipeline implemented using Snow park Python. The pipeline involves multiple Data Frame transformations, joins, aggregations, and window functions. To enhance the maintainability and readability of the code, you want to modularize the pipeline into reusable functions. You also need to handle potential errors and exceptions gracefully. Consider the following code snippet:

Question49: You have a data pipeline that aggregates web server logs hourly. The pipeline loads data into a Snowflake table 'WEB LOGS' which is partitioned by 'event_time'. You notice that queries against this table are slow, especially those that filter on specific time ranges. Analyze the following Snowflake table definition and query pattern and select the options to diagnose and fix the performance issue: Table Definition:

Question50: You are developing a Secure UDF in Snowflake to encrypt sensitive customer data'. The UDF should only be accessible by authorized roles. Which of the following steps are essential to properly secure the UDF?

Question51: You are setting up a Kafka connector to load data from a Kafka topic into a Snowflake table. You want to use Snowflake's automatic schema evolution feature to handle potential schema changes in the Kafka topic. Which of the following is the correct approach to enable and configure automatic schema evolution using the Kafka Connector for Snowflake?

Question52: You are designing a data recovery strategy for a critical table 'CUSTOMER DATA' in your Snowflake environment. The data in this table is highly sensitive, and regulatory requirements mandate a retention period of at least 90 days for potential audits. You need to configure the Time Travel retention period to meet these requirements. What is the maximum supported Time Travel retention period, and how would you set it at the table level?

Question53: You are designing a data governance strategy for a Snowflake data warehouse. One of the key requirements is to track data lineage for sensitive data, specifically Personally Identifiable Information (PII). You need to understand how PII data flows through various transformations and tables. Which Snowflake feature, when combined with appropriate tagging and metadata management practices, can BEST help you achieve this?

Question54: A Snowflake data pipeline utilizes Snowpipe to ingest JSON data from cloud storage into a raw staging table 'RAW DATA' Subsequently, a series of transformation tasks are executed to cleanse, transform, and load the data into fact and dimension tables. You've noticed significant performance degradation in the transformation tasks, especially when dealing with large JSON payloads and deeply nested structures. Which of the following optimization techniques, applied at different stages of the pipeline, would MOST likely improve the overall performance of the data transformation tasks?

Question55: You are tasked with creating a Snowpark Java stored procedure to calculate a complex, custom rolling average for a time series dataset. This rolling average requires access to external libraries for statistical calculations. Which of the following steps are necessary to successfully deploy and execute this stored procedure?

Question56: You're tasked with building an external function in Snowflake that calls an API to enrich customer data with geographical information (latitude and longitude) based on their IP address. The API endpoint requires an API key passed in the headen Your external function definition looks like this: "'sql CREATE OR REPLACE EXTERNAL FUNCTION VARCHAR) RETURNS VARIANT VOLATILE MAX BATCH ROWS = 100 RETURNS NULL ON NULL INPUT API INTEGRATION = AS 'https://api.example.com/geo'; Which of the following steps are essential to ensure the external function correctly passes the API key to the external service, handles rate limiting from the API, and correctly parses the JSON response from the external service (Assume the API returns a JSON object with 'latitude' and 'longitude' fields)?

Question57: You are tasked with creating a system to monitor the data quality of a 'SALES DATA" table. The table is updated daily with new sales records, and you need to ensure that the 'SALE AMOUNT column always contains positive values. You decide to use Snowflake tasks and streams for this purpose. Consider the following Snowflake script. What is the most appropriate way to modify the 'SALES DATA' table so the task has a 'WHEN' clause that runs only if the 'SALE AMOUNT has negative values? Assume the stream 'SALES DATA STREAM' is properly configured on 'SALES DATA'.

Question58: A financial institution needs to tokenize sensitive customer data (credit card numbers) stored in a Snowflake table named 'CUSTOMER_DATA before it's consumed by a downstream reporting application. The institution uses an external tokenization service accessible via a REST API. Which of the following approaches is the MOST secure and scalable way to implement tokenization during data loading, minimizing exposure of the raw credit card data within Snowflake?

Question59: You are tasked with implementing data masking on a 'CUSTOMER' table. The requirement is to mask the 'EMAIL' column for all users except those with the 'DATA ADMIN' role. You have the following code snippet. What is wrong with it?

Question60: You are tasked with ingesting a large volume of CSV files from an external stage into a Snowflake table. Some of these CSV files contain corrupted records with inconsistent delimiters or missing values. You need to ensure that only valid records are loaded into the table, and the corrupted records are captured for further analysis. Which of the following COPY INTO options would BEST address this requirement?

Question61: You have a Python UDF in Snowflake designed to enrich customer data by calling an external API to retrieve additional information based on the customer ID. Due to API rate limits, you need to implement a mechanism to cache API responses within the UDF to avoid exceeding the limits. The UDF is defined as follows:

Which caching mechanism can be implemented MOST effectively WITHIN the Python UDF to minimize API calls while adhering to Snowflake's UDF limitations?

Question62: You are designing a data loading process for a high-volume streaming data source. The data arrives as Avro files in an AWS S3 bucket. You need to load this data into a Snowflake table with minimal latency and operational overhead. Which of the following combinations of Snowflake features and configurations would be MOST suitable for this scenario? (Select TWO)

Question63: Given the following scenario: You have an external table 'EXT SALES in Snowflake pointing to a data lake in Azure Blob Storage. The storage account network rules are configured to only allow specific IP addresses and virtual network subnets, enhancing security. You are getting intermittent errors when querying 'EXT SALES. Which of the following could be the cause(s) and the corresponding solution(s)? Select all that apply.

Question64: You are using the Snowflake Developer API to automate the creation and management of masking policies. You need to create a masking policy that masks an email address using SHA256 hashing. You also want to ensure that the policy can be applied to multiple tables and columns without modification. Assuming you have already established a connection to Snowflake using the Developer API, which of the following code snippets BEST demonstrates how to create and apply this masking policy using Python?

Question65: Consider the following Snowflake Javascript UDF designed to convert temperatures between Celsius and Fahrenheit:

Which statement regarding the UDF's security and behavior is MOST accurate?

Question66: A large e-commerce company is experiencing performance issues with its daily sales report queries. These queries aggregate data from a fact table 'SALES FACT (100 billion rows) and several dimension tables, including 'CUSTOMER DIM', 'PRODUCT DIM', and 'DATE DIM'. The queries are run every morning and are essential for business decision-making. The team has identified that the 'SALES FACT table's primary key is 'SALE ID, but the queries frequently filter and join on 'CUSTOMER and 'PRODUCT ID. You want to use query acceleration service for these reports without changing query logic. Which combination of actions will MOST effectively leverage query acceleration service, assuming sufficient credits?

Question67: You have a Snowflake Stream named 'PRODUCT CHANGES' created on a table 'PRODUCTS'. A downstream task attempts to consume records from the stream, but occasionally fails with a 'Table PRODUCTS has been altered' error. The 'PRODUCTS' table undergoes DDL changes (e.g., adding/dropping columns) infrequently, but these changes are necessary for evolving business requirements. How can you design a more resilient data pipeline that minimizes disruptions caused by DDL changes to the 'PRODUCTS' table while still leveraging the 'PRODUCT CHANGES' stream?

Question68: A data engineer is tasked with implementing a data governance strategy in Snowflake. They need to automatically apply a tag 'PII CLASSIFICATION' to all columns containing Personally Identifiable Information (PII). Given the following requirements: 1. The tag must be applied as close to data ingestion as possible. 2. The tagging process should be automated and scalable. 3. The tag value should be dynamically set based on a regular expression match against column names and data types. Which of the following approaches would be MOST effective and efficient in achieving these goals?

Question69: You are tasked with loading a large dataset (50TB) of JSON files into Snowflake. The JSON files are complex, deeply nested, and irregularly structured. You want to maximize loading performance while minimizing storage costs and ensuring data integrity. You have a dedicated Snowflake virtual warehouse (X-Large).
Which combination of approaches would be MOST effective?

Question70: Consider a scenario where you have a Snowflake table named 'CUSTOMER DATA' containing customer IDs (INTEGER) and encrypted credit card numbers (VARCHAR). You need to create a secure JavaScript UDF to decrypt these credit card numbers using a custom encryption key stored securely within Snowflake's internal stage, and then mask all but the last four digits of the decrypted number for data protection. Which of the following actions are necessary to ensure both functionality and security while adhering to Snowflake's best practices for UDF development and security?

Question71: Your team is developing a set of complex analytical queries in Snowflake that involve multiple joins, window functions, and aggregations on a large table called 'TRANSACTIONS. These queries are used to generate daily reports. The query execution times are unacceptably high, and you need to optimize them using caching techniques. You have identified that the intermediate results of certain subqueries are repeatedly used across different reports, but they are not explicitly cached. Given the following options, which combination of strategies would MOST effectively utilize Snowflake's caching capabilities to optimize these analytical queries and improve report generation time?

Question72: You are designing a data sharing solution where the consumer account needs real-time access to a secure view that aggregates data from several tables in your provider account. The consumer should not be able to see the underlying tables. Which of the following approaches offers the MOST secure and efficient way to implement this data sharing while minimizing the risk of data leakage and performance impact on your provider account?

Question73: You need to define a UDF in Snowflake that takes a date as input and returns the next business day (Monday-Friday). If the input date is a Friday, the UDF should return the following Monday. If the input date is a Saturday or Sunday, the function should return the following Monday as well. Which of the following UDF definitions correctly implements this logic?

Question74: You're using Snowpark Python to transform data in a Snowflake table called 'employee_data' which includes columns , 'department, 'salary' , and 'performance_rating'. You need to identify the top 3 highest-paid employees within each department based on their salary, but only for departments where the average performance rating is above 4.0. Which of the following approaches using Snowpark efficiently combines window functions, filtering, and aggregations to achieve this?

Question75: You are tasked with optimizing the performance of a Snowflake virtual warehouse used for running several types of queries: short- running analytical queries with strict latency requirements, long-running batch data transformations, and ad-hoc queries from data scientists. The workload is unpredictable, and the team wants to minimize queueing and maximize resource utilization. Which warehouse configuration would be MOST appropriate to handle this mixed workload, minimizing cost and maximizing performance?

Question76: A data engineering team is managing a Snowflake warehouse that supports a high volume of ad-hoc queries from data analysts exploring a large, semi-structured JSON dataset containing website clickstream data'. The query performance is frequently slow, and analysts are complaining about long wait times. The warehouse is already sized appropriately. You have identified that many of the queries filter on nested JSON attributes that are not explicitly indexed. Considering only query acceleration service features, what is the MOST effective approach to improve query performance for these ad-hoc queries without modifying the queries themselves or significantly increasing storage costs?

Question77: A financial institution is using Snowflake to store transaction data for millions of customers. The data is stored in a table named 'TRANSACTIONS with columns such as 'TRANSACTION ID, 'CUSTOMER ID', 'TRANSACTION DATE, 'TRANSACTION_AMOUNT, and 'MERCHANT CATEGORY'. Analysts are running complex analytical queries that often involve filtering transactions by 'TRANSACTION_DATE, 'MERCHANT CATEGORY' , and 'TRANSACTION_AMOUNT ranges. These queries are experiencing performance bottlenecks. The data team wants to leverage query acceleration service to improve performance without significantly altering the existing query patterns. Which of the following actions or combination of actions would be MOST beneficial, considering the constraints and the nature of the queries? (Select TWO)

Question78: You have a base table 'ORDERS' with columns 'ORDER ID, 'CUSTOMER D', 'ORDER DATE, and 'ORDER AMOUNT'. You need to create a view that aggregates the total order amount per customer per month. However, for data governance purposes, you need to ensure that the view only shows data for the last 3 months. What is the MOST efficient and secure way to create this view in Snowflake?

Question79: You're designing a Snowpark Scala stored procedure that must execute a series of complex data quality checks on a Snowflake table.
These checks involve multiple steps, including validating data types, checking for null values, and verifying data consistency against external reference data'. You want to ensure that the stored procedure is resilient to errors, provides detailed logging, and can be easily monitored. Which of the following approaches would be the MOST robust and scalable for handling errors and logging within this Snowpark Scala stored procedure?

Question80: A Snowflake data warehouse contains a table named 'SALES TRANSACTIONS' with the following columns: 'TRANSACTION ID', 'PRODUCT D', 'CUSTOMER D', 'TRANSACTION DATE, and 'SALES AMOUNT'. You need to optimize a query that calculates the total sales amount per product for a given month. The 'SALES TRANSACTIONS' table is very large (billions of rows), and queries are slow. Given the following initial query: SELECT PRODUCT ID, SUM(SALES AMOUNT) AS TOTAL SALES FROM SALES TRANSACTIONS WHERE TRANSACTION DATE BETWEEN '2023-01-07' AND '2023-01-31' GäOUP BY PRODUCT ID; Which of the following actions, when combined, would MOST effectively improve the performance of this query?

Question81: You are tasked with creating a SQL UDF in Snowflake to mask sensitive customer data (email addresses) before it's used in a reporting dashboard. The masking should replace all characters before the '@' symbol with asterisks, preserving the domain part. For example, '[email protected]' should become ' @example.com'. Which of the following SQL UDF definitions correctly implements this masking logic, while minimizing the impact on Snowflake compute resources?

Question82: You are designing a Snowpipe pipeline to ingest data from an AWS SQS queue. The queue contains notifications about new files arriving in an S3 bucket. However, due to network issues, some notifications are delayed, causing Snowpipe to potentially miss files. Which of the following strategies, when combined, will BEST address the problem of delayed notifications and ensure data completeness?

Question83: You are tasked with implementing row-level security (RLS) on a 'SALES' table to restrict access based on the 'REGION' column. Users with the 'NORTH REGION ROLE should only see data where 'REGION = 'NORTH". You've created a row access policy named north_region_policy'. After applying the policy to the 'SALES table, users with the 'NORTH REGION ROLE are still seeing all rows.
Which of the following is the MOST likely reason for this and how can it be corrected?

Question84: You have a Snowpark DataFrame 'df_products' with columns 'product id', 'category', and 'price'. You need to perform the following transformations in a single, optimized query using Snowpark Python: 1. Filter for products in the 'Electronics' or 'Clothing' categories. 2. Group the filtered data by category. 3. Calculate the average price for each category. 4. Rename the aggregated column to 'average_price'. Which of the following code snippets demonstrates the most efficient way to achieve this?

Question85: You are designing a CI/CD pipeline for your Snowflake data transformations. One stage involves testing a new stored procedure that modifies several tables in your data warehouse. To ensure data integrity and proper rollback capabilities during testing in your development environment, you want to use a combination of cloning and Tme Travel. Select the option that represents the most robust strategy for testing with the ability to revert to the original state in case of failures. Choose all that apply.

Question86: You are tasked with building a data pipeline that ingests customer interaction data from multiple microservices using Snowpipe Streaming. Each microservice writes data in JSON format to its own Kafka topic. You need to design an efficient and scalable solution to ingest this data into a single Snowflake table, while ensuring data integrity and minimizing latency. Consider these constraints: 1. High data volume with variable ingestion rates. 2. The need to correlate data from different microservices based on a common 'customer id'. 3. Potential for schema evolution in the microservices. Given these requirements and constraints, which of the following architectural approaches, leveraging Snowpipe Streaming features and Snowflake capabilities, would be the MOST appropriate and robust?

Question87: You have a Snowflake table 'raw_data' with columns 'id', 'timestamp', and 'payload'. A stream is defined on this table. A data pipeline reads changes from the stream and applies transformations before loading the data into a target table. However, the pipeline needs to handle cases where updates to the same 'id' occur multiple times within a short period, and only the latest version of the 'payload' should be processed. How can you achieve this idempotent processing of stream data to ensure only the latest payload is applied to the target table, avoiding duplicates and inconsistencies, using Snowflake streams?

Question88: You are developing a Snowpark Python stored procedure that performs complex data transformations on a large dataset stored in a Snowflake table named 'RAW SALES'. The procedure needs to efficiently handle data skew and leverage Snowflake's distributed processing capabilities. You have the following code snippet:

Which of the following strategies would be MOST effective to optimize the performance of this Snowpark stored procedure, specifically addressing potential data skew in the 'product id' column, assuming 'product_id' is known to cause uneven data distribution across Snowflake's micro-partitions?

Question89: You have a 'SALES table and a 'PRODUCTS table. The 'SALES table contains daily sales transactions, including 'SALE DATE , 'PRODUCT ID', and 'QUANTITY. The 'PRODUCTS table contains 'PRODUCT and 'CATEGORY. You need to create a materialized view to track the total quantity sold per category daily, optimized for fast query performance. You anticipate frequent updates to the 'SALES table but infrequent changes to the 'PRODUCTS table. Which of the following strategies would provide the MOST efficient materialized view implementation, considering both data freshness and query performance?

Question90: A data engineering team uses Snowflake to analyze website clickstream data stored in AWS S3. The data is partitioned by year and month in the S3 bucket. They need to query the data frequently for reporting purposes but don't want to ingest the entire dataset into Snowflake due to storage costs and infrequent full dataset analysis. Which approach is the MOST efficient and cost-effective way to enable querying of this data in Snowflake?

Question91: You have created a Snowflake Iceberg table that points to data in an AWS S3 bucket. After some initial data ingestion, you realize that the schema in the Iceberg table does not perfectly match the schema of the underlying Parquet files in S3. Specifically, one of the columns in the Iceberg table is defined as 'VARCHAR , while the corresponding column in the Parquet files is stored as 'INT. What will be the most likely behavior when you query this Iceberg table in Snowflake?

Question92: You are developing a data pipeline to ingest customer feedback data from a third-party service using the Snowflake REST API. This service imposes rate limits, and exceeding them results in temporary blocking. To handle this, you implement exponential backoff with jitter. Which of the following code snippets BEST demonstrates how to correctly implement exponential backoff with jitter when calling the Snowflake REST API in Python, assuming data)' is a function that makes the API call and raises an exception on rate limiting?

Question93: You are designing a complex data pipeline in Snowflake that involves multiple interdependent Tasks. Several of these Tasks need to access sensitive customer data, and you want to ensure that the least privilege principle is followed. How should you configure the Tasks and their associated roles to minimize the risk of unauthorized data access while maintaining the functionality of the pipeline? (Select TWO)

Question94: You are implementing a data share between two Snowflake accounts. The provider account wants to grant the consumer account access to a function that returns anonymized customer data based on a complex algorithm. The provider wants to ensure that the consumer cannot see the underlying implementation details of the anonymization algorithm. Which of the following approaches can achieve this goal? (Select TWO)

Question95: You have a large dataset stored in AWS S3 in Parquet format. The data is constantly updated by an external process, but you need to run read-only analytical queries against the most current data in Snowflake without ingesting it. Which approach is the MOST efficient and cost-effective way to achieve this, considering minimal latency for query results?

Question96: You have a requirement to create a UDF in Snowflake that transforms data based on a complex set of rules defined in an external Python library. The library requires specific dependencies. You also need to ensure the UDF is secure and that the code is not visible to unauthorized users. Which of the following steps MUST be taken to achieve this?

Question97: A critical database, 'PRODUCTION DB', in your Snowflake account was accidentally dropped. You need to restore it as quickly as possible, but you're unsure if Time Travel retention is sufficient. Which method guarantees restoration of the database even if it falls outside the Time Travel window?

Question98: You have configured a Snowpipe to load data from an AWS S3 bucket into a Snowflake table. The data in S3 is updated frequently. You've noticed that despite the Snowpipe being active and the S3 event notifications being configured correctly, some newly added files are not being picked up by the Snowpipe. You run 'SYSTEM$PIPE and see the 'executionstate' is 'RUNNING' but the 'pendingFileCount' remains at O, even after new files are placed in the S3 bucket. Choose all of the reasons that could explain the observations.

Question99: You're managing a Snowflake data warehouse and need to create a development environment for testing a complex stored procedure that updates a critical table, 'SALES DATA'. The procedure is located in the 'PRODUCTION' database and you want to ensure minimal impact to the production environment during development. You decide to use cloning and time travel. Which of the following strategies is the MOST efficient and safest approach to achieve this, minimizing downtime and resource consumption in production?

Question100: You are developing a Snowpark Python application that needs to process data from a Kafka topic. The data is structured as Avro records. You want to leverage Snowpipe for ingestion and Snowpark DataFrames for transformation. What is the MOST efficient and scalable approach to integrate these components?

Question101: You are developing a data pipeline in Snowflake that uses SQL UDFs for data transformation. You need to define a UDF that calculates the Haversine distance between two geographical points (latitude and longitude). Performance is critical. Which of the following approaches would result in the most efficient UDF implementation, considering Snowflake's execution model?

Question102: You are configuring a Snowflake Data Clean Room for two healthcare providers, 'ProviderA' and 'ProviderB', to analyze patient overlap without revealing Personally Identifiable Information (PII). Both providers have patient data in their respective Snowflake accounts, including a 'PATIENT ID' column that uniquely identifies each patient. You need to create a secure join that allows the providers to determine the number of shared patients while protecting the raw 'PATIENT ID' values. Which of the following approaches is the most secure and efficient way to achieve this using Snowflake features? Select TWO options.

Question103: Consider a scenario where you need to transform data in a Snowflake table using a complex custom transformation logic best implemented in Java'. You decide to use a Snowpark Java UDF. You've packaged your Java code into a JAR file and uploaded it to an internal stage named Which of the following steps are necessary and correctly ordered to deploy and use this Java UDF within Snowflake?

Question104: You are monitoring a Snowpipe pipeline that loads data from an external stage into a Snowflake table. You observe the following error messages in the PIPE ERRORS view: 'Invalid UTF-8 detected in string'. The data files on the stage are encoded in UTF-8. Which of the following actions, taken individually or in combination, are MOST likely to resolve this issue? (Select TWO)

Question105: You are designing a data pipeline using Snowpipe to ingest data from multiple S3 buckets into a single Snowflake table. Each S3 bucket represents a different data source and contains files in JSON format. You want to use Snowpipe's auto-ingest feature and a single Snowpipe object for all buckets to simplify management and reduce overhead. However, each data source has a different JSON schem a. How can you best achieve this goal while ensuring data is loaded correctly and efficiently into the target table?

Question106: You have a Snowflake task that executes a complex stored procedure. This stored procedure performs several UPDATE statements on a large table. After enabling the 'QUERY TAG' parameter, you notice that the task history in Snowflake shows frequent suspensions due to exceeding warehouse resource limits. The warehouse is already scaled to the largest size. Which combination of the following strategies would BEST address this issue and minimize task suspensions, assuming you CANNOT further scale the warehouse?

Question107: You are working on a Snowpark Python application that needs to process a stream of data from Kafka, perform real-time aggregations, and store the results in a Snowflake table. The data stream is highly variable, with occasional spikes in traffic that overwhelm your current Snowpark setup, leading to significant latency in processing. Which of the following strategies, either individually or in combination, would be MOST effective to handle these traffic spikes and ensure near real-time processing?

Question108: You are tasked with implementing column-level security on the 'EMPLOYEE table to restrict access to the 'SALARY column. Only users with the 'HR ROLE' should be able to view the actual salary. All other users should see NULL. You create a masking policy as follows:

What additional steps are necessary to enforce this policy?

Question109: You have a requirement to continuously load data from a cloud storage location into a Snowflake table. The source data is in Avro format and is being appended to the cloud storage location frequently. You want to automate this process using Snowpipe. You've already created the Snowpipe and the associated stage and file format. However, you notice that some files are being skipped during the ingestion process, and data is missing in your Snowflake table. What is the MOST likely reason for this issue, assuming all necessary permissions and configurations (stage, file format, pipe definition) are correctly set up?

Question110: You are managing a Snowflake environment where data retention is set to the default 1 day for all databases and tables. You need to clone a production table, 'CUSTOMER DATA, to a development environment to test some complex transformations. However, after cloning, you realize that the original 'CUSTOMER DATA' table in production was accidentally dropped 2 days ago. Which of the following statements accurately describe the situation and what can be done, if anything?

Question111: Consider the following Snowflake UDTF definition written in Python:

Which of the following statements are TRUE regarding the deployment and usage of this UDTF?

Question112: You have a large dataset of JSON documents stored in AWS S3, each document representing a customer order. You want to ingest these documents into Snowflake using Snowpipe and transform the nested 'address' field into separate columns in your target table. Considering data volume, complexity, and cost efficiency, which approach is MOST suitable?

Question113: You have a table named 'TRANSACTIONS with the following definition: CREATE TABLE TRANSACTIONS ( TRANSACTION ID NUMBER, TRANSACTION DATE DATE, CUSTOMER_ID NUMBER, AMOUNT PRODUCT_CATEGORY VARCHAR(50) Users frequently query this table using filters on both 'TRANSACTION_DATE and 'PRODUCT CATEGORY. You want to optimize query performance. What is the MOST effective approach?

Question114: You are tasked with designing a data sharing solution where data from multiple tables residing in different databases within the same Snowflake account needs to be combined into a single view that is then shared with a consumer account. The view must also implement row-level security based on the consumer's role. Which of the following options represent valid approaches for implementing this solution? Select all that apply.

Question115: A data engineer is working with a Snowpark DataFrame 'sales df containing sales data with columns 'product id', 'sale_date', and 'sale amount'. The engineer needs to calculate the cumulative sales amount for each product over time. Which of the following code snippets using window functions correctly calculates the cumulative sales amount, ordered by 'sale date'?

Question116: You have a Snowflake view that joins three large tables: ORDERS, CUSTOMERS, and PRODUCTS. The query accessing this view is frequently used but performs poorly. You suspect inefficient join processing and potential skew in the data'. Which of the following strategies can be used to optimize the view's performance? (Select all that apply)

Question117: A data engineering team is using a Snowflake stream to capture changes made to a source table named 'orders'. They want to only capture 'INSERT and 'UPDATE operations but exclude 'DELETE operations from being captured in the stream. Which of the following configurations will achieve this requirement? Assume the stream has already been created and is named 'orders_stream'.

Question118: You are tasked with ingesting data from an external stage into Snowflake. The data is in JSON format and compressed using GZIP. The JSON files contain nested arrays. You need to create a file format object that Snowflake can use to properly parse the dat a. Which of the following options represents the MOST efficient and correct file format definition to achieve this? Assume the stage is already created and accessible.

Question119: A financial services company stores sensitive customer data, including credit card numbers, in a Snowflake table called 'CUSTOMER DATA. You need to implement dynamic data masking on the 'CREDIT CARD NUMBER column. You want to ensure that only users with the FINANCE ADMIN' role can view the unmasked credit card numbers. All other users should see a masked version of the data'. Which of the following set of commands is the MOST efficient and secure way to achieve this?

Question120: You are implementing row access policies on a 'SALES DATA table to restrict access based on the 'REGION' column. Different users are allowed to see data only for specific regions. You have a mapping table 'USER REGION MAP' with columns 'USERNAME' and 'REGION'. You want to create a row access policy that dynamically filters the 'SALES DATA' based on the user and their allowed region. Which of the following options represents a correct approach to create and apply this row access policy?

Question121: You are tasked with optimizing a data pipeline that loads data from an external cloud storage location into Snowflake, transforms it, and then loads it into reporting tables. The pipeline is experiencing intermittent performance issues. You want to proactively identify and address these issues. Which of the following monitoring techniques and Snowflake features would be MOST effective for continuous monitoring and performance optimization?

Question122: A data engineer is facing performance issues with a complex analytical query in Snowflake. The query joins several large tables and uses multiple window functions. The query profile indicates that a significant amount of time is spent in the 'Remote Spill' stage. This means the data from one of the query stages is spilling to the remote disk. What are the possible root causes for 'Remote Spill' and what steps can be taken to mitigate this issue? Select two options.

Question123: A data engineer observes that a daily data transformation pipeline in Snowflake, which processes data from external stage 's3://my- bucket/raw_dataP , is consistently taking longer to complete. Upon investigation, the engineer finds that the COPY INTO statement is the bottleneck. The COPY INTO statement is as follows:

Which of the following could be the root cause of the performance degradation and how would you address them? Select two options.

Question124: Which of the following statements are TRUE regarding Snowflake's Fail-safe mechanism and its relation to Time Travel? (Select all that apply)

Question125: You have created a masking policy called which redacts salary information based on the user's role. You have applied this policy to the 'SALARY column in the 'EMPLOYEES table. However, after applying the policy, you notice that even users with the 'ACCOUNTADMIN' role are seeing the masked data, which is not the intended behavior. The intention is that 'ACCOUNTADMIN' and 'SECURITYADMIN' roles should always see the real salary data'. What is the MOST likely cause of this issue and what would you suggest fix that?

Question126: You have a Snowflake Task that is designed to transform and load data into a target table. The task relies on a Stream to detect changes in a source table. However, you notice that the task is intermittently failing with a 'Stream STALE' error, even though the data in the source table is continuously updated. What are the most likely root causes and the best combination of solutions to prevent this issue? (Select TWO)

Question127: A company is using Snowflake's web app interface to manage its data'. A data engineer needs to create a new table, load data into it from a CSV file stored in an internal stage, and then grant SELECT privileges on the table to a specific role using the web app. Which sequence of actions within the Snowflake web app represents the most efficient and secure way to accomplish this task?

Question128: You have a Snowflake table named 'ORDERS clustered on 'ORDER DATE. After a significant data load, you want to evaluate the effectiveness of the clustering. Which of the following SQL queries, using Snowflake system functions, will provide insights into the clustering depth and overlap of micro-partitions in the 'ORDERS' table, specifically helping you identify whether re-clustering is necessary? Assume that the table

Question129: A Snowflake table 'PRODUCT REVIEWS' is being ingested into from an external system. You have a stream 'PRODUCT REVIEWS STREAM' defined on this table to capture changes. Due to a bug in the ingestion process, incorrect data was loaded for a specific period. You need to correct the data'. Which of the following SQL statements, when executed against the 'PRODUCT REVIEWS STREAM' , will return the number of rows that were inserted, updated, and deleted during that period?

Question130: You're tasked with optimizing a Snowflake data pipeline that transforms and loads data into a target table. The pipeline uses a series of complex SQL queries with multiple joins and aggregations. After analyzing the query execution plans, you identify a few key bottlenecks. Which of the following optimization techniques would MOST directly address common performance bottlenecks in such a data pipeline within Snowflake?

Question131: A Snowflake data engineer is troubleshooting a slow-running query that joins two large tables, 'ORDERS' (1 billion rows) and 'CUSTOMER' (10 million rows), using the 'CUSTOMER ID' column. The query execution plan shows a significant amount of data spilling to local disk. The query is as follows:

Which of the following are the MOST likely root causes of the disk spilling and the best corresponding solutions? Select two options that directly address the disk spilling issue.

Question132: You are tasked with sharing a subset of a customer table (CUSTOMER DATA') residing in your organization's Snowflake account with a partner organization. You need to mask personally identifiable information (PII) while providing near real-time updates. You decide to use a secure view. Which of the following SQL statements is the MOST efficient and secure way to accomplish this, assuming the partner only needs 'customer id', 'masked_email', 'city', and 'state'? The email should be masked using SHA256.

Question133: A data warehousing team is experiencing inconsistent query performance on a large fact table C SALES FACT) that is updated daily. Some queries involving complex joins and aggregations take significantly longer to execute than others, even when run with the same virtual warehouse size. You suspect that the query result cache is not being effectively utilized due to variations in query syntax and the dynamic nature of the data'. Which of the following strategies could you implement to maximize the effectiveness of the query result cache and improve query performance consistency? Assume virtual warehouse size is large and the data is skewed across days.

Question134: You have a Snowflake table 'CUSTOMER DATA with a column 'EMAIL' containing customer email addresses. You need to classify this column as 'PII' using a tag named 'SENSITIVITY' and value 'CONFIDENTIAL'. Also, you want all queries accessing this 'EMAIL' column to be logged, with specific details about who accessed it and when. You already have appropriate roles and privileges to perform the required operations. Which of the following SQL statements, when executed in sequence, will achieve this goal, assuming appropriate logging mechanisms are already configured to read from the Snowflake ACCESS HISTORY view?

Question135: You are designing a system to monitor data access patterns in Snowflake. You want to capture detailed information about all queries executed, including the user, query text, execution time, and any potential data access violations based on security policies. Which of the following approaches, used in combination, would provide the MOST comprehensive and scalable solution for this monitoring requirement? (Select TWO)

Question136: You are responsible for ensuring data consistency across multiple Snowflake tables involved in a financial reporting system. You've noticed discrepancies in aggregate calculations between a 'TRANSACTIONS" table and a summary table 'MONTHLY REPORTS'. The 'TRANSACTIONS' table is frequently updated via streams and tasks. Which combination of the following strategies would be MOST effective in identifying and resolving these inconsistencies in near real-time?

Question137: You are designing a continuous data pipeline to load data from AWS S3 into Snowflake. The data arrives in near real-time, and you need to ensure low latency and minimal impact on your Snowflake warehouse. You plan to use Snowflake Tasks and Streams. Which of the following approaches would provide the most efficient and cost-effective solution for this scenario, considering data freshness and resource utilization?

Question138: You are designing a data pipeline to load JSON data from an AWS S3 bucket into a Snowflake table. The JSON files have varying schemas, and you want to use schema evolution to handle changes. You are using a named external stage with 'AUTO REFRESH = TRUE. You notice that some files are not being ingested, and the COPY HISTORY shows 'Invalid JSON' errors. Which of the following actions would BEST address this issue while minimizing manual intervention?

Question139: You are tasked with building a robust data quality monitoring system for a Snowflake data pipeline. The pipeline processes customer order data and loads it into a 'CUSTOMER ORDERS table. You need to implement checks to ensure that certain critical columns (e.g., 'ORDER ID, 'CUSTOMER ID', 'ORDER DATE, meet specific data quality requirements (e.g., not null, valid format, within acceptable range). You want to design a flexible and scalable solution that allows you to easily add, modify, and monitor data quality rules. Select the options to implement that and scale efficiently Assume there is a central Data Quality table for each metrics

Question140: You are tasked with implementing a data loading process for a table 'CUSTOMER DATA' in Snowflake. The source data is in Parquet format on Azure Blob Storage and contains personally identifiable information (PII). You must ensure that the data is loaded securely, masked during the loading process, and that only authorized users can access the unmasked data after the load. Assume you have already created a stage pointing to the Azure Blob Storage. Which of the following steps should you take to achieve this?

Question141: You are designing a data pipeline that uses the Snowflake SQLAPI to execute a series of complex SQL queries. These queries involve multiple joins, aggregations, and user-defined functions (UDFs). You need to ensure that the pipeline is resilient to transient network errors and can handle a large volume of concurrent requests. Which of the following strategies would you implement to enhance the reliability and performance of your pipeline?

Question142: You have implemented external tokenization for a sensitive data column in Snowflake using a UDF that calls an external API. After some time, you discover that the external tokenization service is experiencing intermittent outages, causing queries using the tokenized column to fail. What is the BEST approach to mitigate this issue and maintain data availability while minimizing the risk of exposing the raw data?

Question143: A data engineer is tasked with creating a Snowpark Python UDF to perform sentiment analysis on customer reviews. The UDF, named 'analyze_sentiment' , takes a string as input and returns a string indicating the sentiment ('Positive', 'Negative', or 'Neutral'). The engineer wants to leverage a pre-trained machine learning model stored in a Snowflake stage called 'models'. Which of the following code snippets correctly registers and uses this UDF?

Question144: You are tasked with migrating data from a legacy SQL Server database to Snowflake. One of the tables, 'ORDERS' , contains a column 'ORDER DETAILS that holds concatenated string data representing multiple order items. The data is formatted as 'iteml :qtyl ;item2:qty2;...'. You need to transform this string data into a JSON array of objects, where each object represents an item with 'name' and 'quantity' fields. Which of the following steps and functions would you use in Snowflake to achieve this transformation, in addition to loading the data?

Question145: A data engineering team is responsible for processing a high volume of semi-structured JSON data ingested daily into Snowflake. The ingestion process currently uses a single 'X-Large' virtual warehouse. During peak hours, the data loading latency increases significantly, impacting downstream reporting. The team is considering either scaling up to a '3X-Large' warehouse or scaling out by creating a multi- cluster warehouse with a minimum of 2 and a maximum of 4 'X-Large' clusters. Which of the following factors should be prioritized when making this decision to optimize performance, considering cost and concurrency requirements?

Question146:

Question147: You are using Snowpark Python to transform a DataFrame 'df_orderS containing order data'. You need to filter the DataFrame to include only orders with a total amount greater than $1000 and placed within the last 30 days. Assume the DataFrame has columns 'order_id', 'order_date' (timestamp), and 'total_amount' (numeric). Which of the following code snippets is the MOST efficient and correct way to achieve this filtering using Snowpark?

Question148: A data engineer is using the Snowflake Spark connector to read a large table from Snowflake into a Spark DataFrame. The table contains a 'TIMESTAMP NTT column. After loading the data, the engineer observes that the values in the 'TIMESTAMP NTZ' column are not preserved accurately when retrieved from the DataFrame. What are the potential issues and what configurations can be adjusted in Snowflake to improve the result?

Question149: You are designing a data pipeline that involves unloading large amounts of data (hundreds of terabytes) from Snowflake to AWS S3 for archival purposes. To optimize cost and performance, which of the following strategies should you consider? (Select ALL that apply)